Visual Basic Source Code/Visual Basic Source Code.iso/vbsource/mquery/mjoin.frm

Text File | 1995-05-02 | 8KB | 261 lines

VERSION 2.00
Begin Form fJoin
   BackColor       =   &H00C0C0C0&
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Join Tables"
   ClientHeight    =   3900
   ClientLeft      =   2580
   ClientTop       =   2250
   ClientWidth     =   7620
   ControlBox      =   0   'False
   Height          =   4305
   Left            =   2520
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3900
   ScaleWidth      =   7620
   Top             =   1905
   Width           =   7740
   Begin Frame Frame1
      BackColor       =   &H00C0C0C0&
      Caption         =   "Select type of Join"
      Height          =   2175
      Left            =   120
      TabIndex        =   8
      Top             =   1560
      Width           =   7335
      Begin OptionButton rRightjoin
         BackColor       =   &H00C0C0C0&
         Caption         =   "Option1"
         Height          =   255
         Left            =   120
         TabIndex        =   14
         Top             =   1440
         Width           =   255
      End
      Begin OptionButton rleftjoin
         BackColor       =   &H00C0C0C0&
         Caption         =   "Option1"
         Height          =   255
         Left            =   120
         TabIndex        =   13
         Top             =   840
         Width           =   255
      End
      Begin OptionButton rEqijoin
         BackColor       =   &H00C0C0C0&
         Caption         =   "Option1"
         Height          =   255
         Left            =   120
         TabIndex        =   12
         Top             =   360
         Value           =   -1  'True
         Width           =   255
      End
      Begin Label Label3
         BackColor       =   &H00C0C0C0&
         Caption         =   "Right  Join Include all rows from the right table and only those rows in the left  table where joined fields are equal"
         Height          =   495
         Left            =   600
         TabIndex        =   11
         Top             =   1440
         Width           =   6495
      End
      Begin Label Label2
         BackColor       =   &H00C0C0C0&
         Caption         =   "Left Join Include all rows from  left table and only those rows in the right table where joined fields are equal"
         Height          =   495
         Left            =   600
         TabIndex        =   10
         Top             =   840
         Width           =   6495
      End
      Begin Label Label1
         BackColor       =   &H00C0C0C0&
         Caption         =   "Equi-Join Include all rows from both tables where joined fields are equal."
         Height          =   375
         Left            =   600
         TabIndex        =   9
         Top             =   360
         Width           =   6495
      End
   End
   Begin CommandButton ClearJoinsButton
      BackColor       =   &H00C0C0C0&
      Caption         =   "C&lear All Joins"
      Height          =   375
      Left            =   5880
      TabIndex        =   7
      Top             =   720
      Width           =   1575
   End
   Begin CommandButton CloseButton
      BackColor       =   &H00C0C0C0&
      Cancel          =   -1  'True
      Caption         =   "&Close"
      Height          =   375
      Left            =   5880
      TabIndex        =   6
      Top             =   1200
      Width           =   1575
   End
   Begin ListBox cFieldList2
      BackColor       =   &H00FFFFFF&
      Height          =   1200
      Left            =   3960
      TabIndex        =   4
      Tag             =   "OL"
      Top             =   240
      Width           =   1815
   End
   Begin ListBox cFieldList1
      BackColor       =   &H00FFFFFF&
      Height          =   1200
      Left            =   2040
      TabIndex        =   3
      Tag             =   "OL"
      Top             =   240
      Width           =   1815
   End
   Begin CommandButton AddJoinButton
      BackColor       =   &H00C0C0C0&
      Caption         =   "&Add Join to Query"
      Enabled         =   0   'False
      Height          =   375
      Left            =   5880
      TabIndex        =   1
      Top             =   240
      Width           =   1575
   End
   Begin ListBox cTableList
      BackColor       =   &H00FFFFFF&
      Height          =   1200
      Left            =   120
      MultiSelect     =   1  'Simple
      TabIndex        =   0
      Tag             =   "OL"
      Top             =   240
      Width           =   1815
   End
   Begin Label FieldsLabel
      Alignment       =   2  'Center
      BackColor       =   &H00C0C0C0&
      Caption         =   "Select Fields to Join Selected Tables on:"
      Height          =   192
      Left            =   2040
      TabIndex        =   5
      Top             =   0
      Width           =   3732
   End
   Begin Label TableListLabel
      BackColor       =   &H00C0C0C0&
      Caption         =   "Select Table Pair:"
      Height          =   192
      Left            =   120
      TabIndex        =   2
      Top             =   0
      Width           =   1812
   End
End
Option Explicit
Dim FTbl1 As String
Dim FTbl2 As String

Sub AddJoinButton_Click ()
  Dim i As Integer

   ' check for type of join

    Select Case reqijoin.Value ' equi join
    Case True
    fQuery.cJoinFields.AddItem FTbl1 + " INNER JOIN " + FTbl2 + " ON " + FTbl1 + "." + "[" + cFieldList1 + "]" + "=" + FTbl2 + "." + "[" + cFieldList2 + "]"
    End Select

    Select Case rleftjoin.Value ' left join
    Case True
    fQuery.cJoinFields.AddItem FTbl1 + " LEFT JOIN " + FTbl2 + " ON " + FTbl1 + "." + "[" + cFieldList1 + "]" + "=" + FTbl2 + "." + "[" + cFieldList2 + "]"
    End Select

    Select Case rRightjoin.Value ' right join
    Case True
    fQuery.cJoinFields.AddItem FTbl1 + " RIGHT JOIN " + FTbl2 + " ON " + FTbl1 + "." + "[" + cFieldList1 + "]" + "=" + FTbl2 + "." + "[" + cFieldList2 + "]"
    End Select

  For i = 0 To cTableList.ListCount - 1
    cTableList.Selected(i) = False
  Next
End Sub

Sub cFieldList1_Click ()
  If cFieldList2 <> "" Then
    AddJoinButton.Enabled = True
  End If
End Sub

Sub cFieldList2_Click ()
  If cFieldList1 <> "" Then
    AddJoinButton.Enabled = True
  End If
End Sub

Sub ClearJoinsButton_Click ()
  fQuery.cJoinFields.Clear
End Sub

Sub CloseButton_Click ()
  Unload Me
End Sub

Sub cTableList_Click ()
  Dim i As Integer
  Dim t As TableDef

  FTbl1 = ""
  FTbl2 = ""
  cFieldList1.Clear
  cFieldList2.Clear

  For i = 0 To cTableList.ListCount - 1
    If cTableList.Selected(i) Then
      If FTbl1 = "" Then
        FTbl1 = cTableList.List(i)
      Else
        FTbl2 = cTableList.List(i)
        Exit For
      End If
    End If
  Next

  If FTbl2 = "" Then Exit Sub   'only one table selected

  Set t = gCurrentDB.TableDefs(FTbl1)
  For i = 0 To t.Fields.Count - 1
    cFieldList1.AddItem t.Fields(i).Name
  Next

  Set t = gCurrentDB.TableDefs(FTbl2)
  For i = 0 To t.Fields.Count - 1
    cFieldList2.AddItem t.Fields(i).Name
  Next

End Sub

Sub Form_Load ()
  Dim i As Integer

  For i = 0 To fQuery.cTableList.ListCount - 1
    If fQuery.cTableList.Selected(i) Then
      cTableList.AddItem fQuery.cTableList.List(i)
    End If
  Next
  'Top = VDMDI.Top + fQuery.Top + fQuery.cCriteria.Top + 1300
  'Left = fQuery.Left + 1500

End Sub

Sub Form_Paint ()
  Outlines Me
End Sub